﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;

namespace DAL
{
    public class TradeDAL
    {
        /// <summary>
        /// 添加到购物车
        /// </summary>
        /// <returns></returns>
        public static int addTrade(int BID,int MID,int number)
        {
            string sql = string.Format("insert into Trade values({0},{1},{2})",BID,MID,number);
            return DBHelp.MyExecuteNonQuery(sql,null);
        }
        /// <summary>
        /// 根据用户id和多个书籍id显示结账数据
        /// </summary>
        /// <param name="MID"></param>
        /// <returns></returns>
        public static List<Trade> getTradeByMIDAndBIDS(int MID,string ids)
        {
            List<Trade> list = new List<Trade>();
            string sql = string.Format("select * from Trade inner join Book on Trade.BID=Book.BID where MID={0} and Trade.TID in ({1})", MID, ids);
            SqlDataReader sdr = DBHelp.MyExecuteReader(sql, null);
            while (sdr.Read())
            {
                Trade t = new Trade();
                t.TID = Convert.ToInt32(sdr["TID"]);
                t.BID = Convert.ToInt32(sdr["BID"]);
                t.MID = Convert.ToInt32(sdr["MID"]);
                t.BCount = Convert.ToInt32(sdr["BCount"]);
                t.BName=sdr["BName"].ToString();
                t.BPic=sdr["BPic"].ToString();
                t.BPrice = Convert.ToInt32(sdr["BPrice"]);
                list.Add(t);
            }
            return list;
        }
        /// <summary>
        /// 根据id显示购物车
        /// </summary>
        /// <param name="MID"></param>
        /// <returns></returns>
        public static List<Trade> getTradeByMID(int MID)
        {
            List<Trade> list = new List<Trade>();
            string sql = "select * from Trade inner join Book on Trade.BID=Book.BID where MID=@MID";
            SqlParameter[] pare = new SqlParameter[]
            {
                new SqlParameter("@MID",MID)
            };
            SqlDataReader sdr = DBHelp.MyExecuteReader(sql, pare);
            while (sdr.Read())
            {
                Trade t = new Trade();
                t.TID = Convert.ToInt32(sdr["TID"]);
                t.BID = Convert.ToInt32(sdr["BID"]);
                t.MID = Convert.ToInt32(sdr["MID"]);
                t.BCount = Convert.ToInt32(sdr["BCount"]);
                t.BName = sdr["BName"].ToString();
                t.BPic = sdr["BPic"].ToString();
                t.BPrice = Convert.ToInt32(sdr["BPrice"]);
                list.Add(t);
            }
            return list;
        }
        /// <summary>
        /// 根据用户Mid查询购物车共有多小条数据
        /// </summary>
        /// <param name="MID"></param>
        /// <returns></returns>
        public static int getTradeRows(int MID)
        {
            SqlParameter[] pare = new SqlParameter[]
            {
                new SqlParameter("@MID",MID)
            };
            try
            {
                return Convert.ToInt32(DBHelp.MyExecuteScalar("select count(*) from Trade where MID=@MID", pare));
            }
            catch (Exception)
            {

                return 0;
            }
        }
        /// <summary>
        /// 根据MID查询购物车里面的总价
        /// </summary>
        /// <param name="MID"></param>
        /// <returns></returns>
        public static double getTradeSumPriceByMID(int MID)
        {
            SqlParameter[] pare = new SqlParameter[]
            {
                new SqlParameter("@MID",MID)
            };
            try
            {
                return Convert.ToDouble(DBHelp.MyExecuteScalar("select sum(Trade.BCount*BPrice) from Trade inner join Book on Trade.BID=Book.BID where MID=@MID", pare));
            }
            catch (Exception)
            {

                return 0;
            }
        }
        /// <summary>
        /// 根据id删除购物车
        /// </summary>
        /// <param name="TID"></param>
        /// <returns></returns>
        public static int DeleteByTID(int TID)
        {
            SqlParameter[] pare = new SqlParameter[]
            {
                new SqlParameter("@TID",TID)
            };

            return DBHelp.MyExecuteNonQuery("delete  from Trade where TID=@TID",pare);
        }
        /// <summary>
        /// 修改数量
        /// </summary>
        /// <returns></returns>
        public static int UpdateByTID(int TID, int BCount)
        {
            SqlParameter[] pare = new SqlParameter[]
            {
                new SqlParameter("@TID",TID),
                new SqlParameter("@BCount",BCount)
            };
            string sql="update Trade set BCount=@BCount where BID=@TID";
            return DBHelp.MyExecuteNonQuery(sql, pare);
        }
    }
}
